﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using NPOI.HSSF.Util;
using System.Web;
using System.Data;
using Microsoft.JScript;
using System;
using System.Drawing;
using NPOI.SS.Util;

public class DataTableToExcel
{
    /// <summary>
    /// 自动设置Excel列宽
    /// </summary>
    /// <param name="sheet"></param>
    protected void AutoSizeColumns(ISheet sheet)
    {
        if (sheet.PhysicalNumberOfRows > 0)
        {
            IRow headerRow;
            headerRow = sheet.GetRow(0);
        }
    }


    /// <summary>
    /// 输出文件到浏览器
    /// </summary>
    /// <param name="ms">Excel文档流</param>
    /// <param name="context">HTTP上下文</param>
    /// <param name="fileName">文件名</param>
    ////<remarks></remarks>
    protected void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
    {
        if (context.Request.Browser.Browser == "IE")
        {
            fileName = HttpUtility.UrlEncode(fileName);
        }
        context.Response.ContentType = "text/plain";
        context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
        context.Response.BinaryWrite(ms.ToArray());
        context.Response.End();
        context.Response.Flush();
        context.Response.Close();
    }

    /// <summary>
    /// DataSet转换成Excel文档流，并输出到客户端
    /// </summary>
    /// <param name="ds"></param>
    /// <param name="context"></param>
    /// <param name="fileName">输出的文件名</param>
    /// <remarks></remarks>
    public void RenderToExcel(DataSet ds, HttpContext context, string fileName)
    {
        MemoryStream ms;
        using (ms = RenderToExcel(ds))
        {
            RenderToBrowser(ms, context, fileName);
        }
    }



    /// <summary>
    /// DataSet转换成Excel文档
    /// </summary>
    /// <param name="ds">数据源</param>
    /// <returns></returns>
    /// <remarks></remarks>
    public MemoryStream RenderToExcel(DataSet ds)
    {
        MemoryStream ms = new MemoryStream();
        IWorkbook workbook;
        workbook = new HSSFWorkbook();

        for (int i = 0; i < ds.Tables.Count; i++)
        {
            DataTable dt1 = ds.Tables[i];
            DataTable dt = ds.Tables[i];
            if (dt.Rows.Count > 0)
            {
                //Sheet页名称
                string strSheetName = dt.TableName;
                //Sheet页合并的单元格数量
                int intMergeCount = 0;
                ISheet sheet;
                ICellStyle cell;

                cell = workbook.CreateCellStyle();

                cell.FillForegroundColor = HSSFColor.White.Index;// 设置单元格样式
                cell.FillPattern = FillPattern.SolidForeground;//背景色
                cell.BorderBottom = BorderStyle.Thin; //设置单元格边框
                cell.BorderLeft = BorderStyle.Thin;
                cell.BorderRight = BorderStyle.Thin;
                cell.BorderTop = BorderStyle.Thin;
                cell.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//Excel文本对齐方式
                cell.WrapText = true;
                

                sheet = workbook.CreateSheet(dt.TableName);//创建sheet
              //设置第一个单元格宽度(单元格号，宽度）

                //设置每个 sheet 页的名称
                IRow firstRow;
                firstRow = sheet.CreateRow(0);
                firstRow.CreateCell(0).SetCellValue(strSheetName);
                firstRow.GetCell(0).CellStyle = cell;// 设置样式



                //合并 sheet 页名称
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count));

                int rowIndex = 1;
                foreach (DataRow row in dt.Rows)
                {
                    if (rowIndex == 1)
                    {
                        IRow headerRow;
                        headerRow = sheet.CreateRow(1);
                        foreach (DataColumn column in dt.Columns)
                        {
                            //sheet.AutoSizeColumn(intMergeCount);
                            sheet.SetColumnWidth(intMergeCount, 70 * 50);
                            headerRow.CreateCell(intMergeCount).SetCellValue(column.Caption);
                            headerRow.GetCell(intMergeCount).CellStyle = cell;// '设置样式
                            intMergeCount += 1;
                          
                        }
                        rowIndex += 1;
                    }


                    //特殊字符会以图片形式显示，故将其分割成不同的单元格显示
                    int intQual = 0;
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    //Excel中增加的Cell索引
                    int intCellIndex = 0;
                    foreach (DataColumn column in dt.Columns)
                    {
                        
                        if (row[column].ToString().Contains("http"))
                        {

                            // sheet.AutoSizeColumn(intCellIndex);  sheet.SetColumnWidth(intMergeCount, 70 * 50);
                            dataRow.CreateCell(intCellIndex);
                            sheet.SetColumnWidth(intCellIndex, 150 * 50);
                            AddPieChart(sheet, dataRow.GetCell(intCellIndex), (HSSFWorkbook)workbook, row[column].ToString(), rowIndex, intCellIndex);
                        }
                        else
                        {
                            dataRow.CreateCell(intCellIndex).SetCellValue(row[column].ToString());
                            //dataRow.GetCell(intCellIndex).CellStyle = cell;
                        }
                        dataRow.GetCell(intCellIndex).CellStyle = cell;
                        intCellIndex += 1;
                    }
                    rowIndex += 1;
                }
                AutoSizeColumns(sheet);
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }

        }

        return ms;
    }
    protected void AddPieChart(ISheet sheet, ICell cell, HSSFWorkbook workbook, string fileurl, int row, int col)
    {
        try
        {
            if (fileurl!="")
            {
                System.Net.WebClient wc = new System.Net.WebClient();
                byte[] bytes = wc.DownloadData(fileurl);
                //int pictreIndex = 0;
                //pictreIndex = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
                //HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                //HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 150, col, row, col + 1, row + 1);
                //HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictreIndex);
                ////HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
                ////HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet(hssfworkbook.GetSheetName(0));

                ////int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);

                ////HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                ////HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, 5, 2, 10, 3);//(255, 125, 1023, 150, 5, 2, 10, 3);//(0, 0, 0, 0, 5, 2, 10, 3);
                ////HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                //pict.Resize();

                int RowSpanCount, ColSpanCount;
                isMergeCell(sheet, row, col, out RowSpanCount, out ColSpanCount);
                CellImage(cell, bytes, RowSpanCount, ColSpanCount);
            }
        }
        catch
        {

        }

    }

    /// <summary>
    /// 图片在单元格等比缩放居中显示
    /// </summary>
    /// <param name="cell">单元格</param>
    /// <param name="value">图片二进制流</param>
    private void CellImage(ICell cell, byte[] value, int RowSpanCount,int ColSpanCount)
    {
        if (value.Length == 0) return;//空图片处理
        double scalx = 0;//x轴缩放比例
        double scaly = 0;//y轴缩放比例
        int Dx1 = 0;//图片左边相对excel格的位置(x偏移) 范围值为:0~1023,超过1023就到右侧相邻的单元格里了
        int Dy1 = 0;//图片上方相对excel格的位置(y偏移) 范围值为:0~256,超过256就到下方的单元格里了
        bool bOriginalSize = false;//是否显示图片原始大小 true表示图片显示原始大小  false表示显示图片缩放后的大小
                                   ///计算单元格的长度和宽度
        double CellWidth = 0;
        double CellHeight = 0;
        //int RowSpanCount = cell.GetSpan().RowSpan;//合并的单元格行数
        //int ColSpanCount = cell.GetSpan().ColSpan;//合并的单元格列数 
        int j = 0;
        for (j = 0; j < RowSpanCount; j++)//根据合并的行数计算出高度
        {
            CellHeight += cell.Sheet.GetRow(cell.RowIndex + j).Height;
        }
        for (j = 0; j < ColSpanCount; j++)
        {
            CellWidth += cell.Row.Sheet.GetColumnWidth(cell.ColumnIndex + j);
        }
        //单元格长度和宽度与图片的长宽单位互换是根据实例得出
        CellWidth = CellWidth / 35;
        CellHeight = CellHeight / 15;
        ///计算图片的长度和宽度
        MemoryStream ms = new MemoryStream(value);
        Image Img = Bitmap.FromStream(ms, true);
        double ImageOriginalWidth = Img.Width;//原始图片的长度
        double ImageOriginalHeight = Img.Height;//原始图片的宽度
        double ImageScalWidth = 0;//缩放后显示在单元格上的图片长度
        double ImageScalHeight = 0;//缩放后显示在单元格上的图片宽度
        if (CellWidth > ImageOriginalWidth && CellHeight > ImageOriginalHeight)//单元格的长度和宽度比图片的大，说明单元格能放下整张图片，不缩放
        {
            ImageScalWidth = ImageOriginalWidth;
            ImageScalHeight = ImageOriginalHeight;
            bOriginalSize = true;
        }
        else//需要缩放，根据单元格和图片的长宽计算缩放比例
        {
            bOriginalSize = false;
            if (ImageOriginalWidth > CellWidth && ImageOriginalHeight > CellHeight)//图片的长和宽都比单元格的大的情况
            {
                double WidthSub = ImageOriginalWidth - CellWidth;//图片长与单元格长的差距
                double HeightSub = ImageOriginalHeight - CellHeight;//图片宽与单元格宽的差距
                if (WidthSub > HeightSub)//长的差距比宽的差距大时,长度x轴的缩放比为1，表示长度就用单元格的长度大小，宽度y轴的缩放比例需要根据x轴的比例来计算
                {
                    scalx = 1;
                    scaly = (CellWidth / ImageOriginalWidth) * ImageOriginalHeight / CellHeight;//计算y轴的缩放比例,CellWidth / ImageWidth计算出图片整体的缩放比例,然后 * ImageHeight计算出单元格应该显示的图片高度,然后/ CellHeight就是高度的缩放比例
                }
                else
                {
                    scaly = 1;
                    scalx = (CellHeight / ImageOriginalHeight) * ImageOriginalWidth / CellWidth;
                }
            }
            else if (ImageOriginalWidth > CellWidth && ImageOriginalHeight < CellHeight)//图片长度大于单元格长度但图片高度小于单元格高度，此时长度不需要缩放，直接取单元格的，因此scalx=1，但图片高度需要等比缩放
            {
                scalx = 1;
                scaly = (CellWidth / ImageOriginalWidth) * ImageOriginalHeight / CellHeight;
            }
            else if (ImageOriginalWidth < CellWidth && ImageOriginalHeight > CellHeight)//图片长度小于单元格长度但图片高度大于单元格高度，此时单元格高度直接取单元格的，scaly = 1,长度需要等比缩放
            {
                scaly = 1;
                scalx = (CellHeight / ImageOriginalHeight) * ImageOriginalWidth / CellWidth;
            }
            ImageScalWidth = 0.5 * CellWidth;
            ImageScalHeight = 0.6 * CellHeight;
        }
        Dx1 = System.Convert.ToInt32((CellWidth - ImageScalWidth) / CellWidth * 1023 / 2);
        Dy1 = System.Convert.ToInt32((CellHeight - ImageScalHeight) / CellHeight * 256 / 2);
        int pictureIdx = cell.Sheet.Workbook.AddPicture((Byte[])value, PictureType.PNG);
        IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor();
        anchor.AnchorType = AnchorType.MoveDontResize;
        anchor.Col1 = cell.ColumnIndex;
        anchor.Col2 = cell.ColumnIndex + RowSpanCount;//  cell.GetSpan().ColSpan;
        anchor.Row1 = cell.RowIndex;
        anchor.Row2 = cell.RowIndex + ColSpanCount;// cell.GetSpan().RowSpan;
        anchor.Dy1 = Dy1;//图片下移量
        anchor.Dx1 = Dx1;//图片右移量，通过图片下移和右移，使得图片能居中显示，因为图片不同文字，图片是浮在单元格上的，文字是钳在单元格里的
        IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch();
        IPicture pic = patriarch.CreatePicture(anchor, pictureIdx);

        if (bOriginalSize)
        {
            pic.Resize();//显示图片原始大小 
        }
        else
        {
            pic.Resize(0.4,0.5);//等比缩放   
        }
    }

    public bool isMergeCell(ISheet sheet, int rowNum, int colNum, out int rowSpan, out int colSpan)
    {
        bool result = false;
        rowSpan = 0;
        colSpan = 0;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int regionsCount = sheet.NumMergedRegions;
        rowSpan = 1;
        colSpan = 1;
        for (int i = 0; i < regionsCount; i++)
        {
            CellRangeAddress range = sheet.GetMergedRegion(i);
            sheet.IsMergedRegion(range);
            if (range.FirstRow == rowIndex && range.FirstColumn == colIndex)
            {
                rowSpan = range.LastRow - range.FirstRow + 1;
                colSpan = range.LastColumn - range.FirstColumn + 1;
                break;
            }
        }
        try
        {
            result = sheet.GetRow(rowIndex).GetCell(colIndex).IsMergedCell;
        }
        catch
        {
        }
        return result;
    }





}
